package com.lovepick.database;

import java.util.ArrayList;
import java.util.List;

import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;

import com.lovepick.bean.MsgListItem;


public class MsgListDbManager
{
	private final String TAG = "MsgListDbManager";
	private DatabaseHelper mDbHelper = null;
	private SQLiteDatabase mDatabase = null;

	public MsgListDbManager()
	{
		mDbHelper = new DatabaseHelper();
	}

	public MsgListItem getMsgListItem(int id)
	{
		//Log.i(TAG, "-----getMsgListItem-----");
		MsgListItem msgListItem = null;
		String sql = "SELECT * FROM " + MsgListTable.TABLE_NAME
				+ " WHERE "
				+ MsgListTable.USER_ID + "=?;";
		String selectionArgs[] = {String.valueOf(id)};
		try
		{

			mDatabase = mDbHelper.getReadableDatabase();
			//Log.i(TAG, sql);
			Cursor cursor = mDatabase.rawQuery(sql, selectionArgs);
			if (cursor.moveToNext())
			{
				msgListItem = new MsgListItem();
				msgListItem.setUserID(id);
				msgListItem.setNickName(cursor.getString(cursor
						.getColumnIndex(MsgListTable.NICKNAME)));
				msgListItem.setLastMsg(cursor.getString(cursor
						.getColumnIndex(MsgListTable.LAST_MSG)));
				msgListItem.setLastDate(cursor.getString(cursor
						.getColumnIndex(MsgListTable.LAST_DATE)));
				msgListItem.setLastID(cursor.getInt(cursor
						.getColumnIndex(MsgListTable.LAST_ID)));
				msgListItem.setHasNew(cursor.getInt(cursor
						.getColumnIndex(MsgListTable.HAS_NEW)));
			}
			else
			{
				msgListItem = null;
			}
			mDatabase.close();
		}
		catch (Exception e)
		{
			// TODO Auto-generated catch block
			//Log.i(TAG, "Exception: " + e.getCause());
			e.printStackTrace();
			msgListItem = null;
		}

		return msgListItem;
	}

	public List<MsgListItem> getMsgListItems()
	{
		//Log.i(TAG, "-----getMsgListItems-----");
		List<MsgListItem> msgListItems = null;
		String sql = "SELECT * FROM " + MsgListTable.TABLE_NAME;
		boolean cursorNull = true;
		try
		{
			mDatabase = mDbHelper.getReadableDatabase();
			//Log.i(TAG, sql);
			Cursor cursor = mDatabase.rawQuery(sql, null);
			msgListItems = new ArrayList<MsgListItem>();
			while (cursor.moveToNext())
			{
				cursorNull = false;
				MsgListItem msgListItem = new MsgListItem();
				int userID = cursor.getInt(cursor.getColumnIndex(MsgListTable.USER_ID));
				msgListItem.setUserID(userID);
				msgListItem.setNickName(cursor.getString(cursor.getColumnIndex(MsgListTable.NICKNAME)));
				msgListItem.setLastMsg(cursor.getString(cursor.getColumnIndex(MsgListTable.LAST_MSG)));
				msgListItem.setLastDate(cursor.getString(cursor.getColumnIndex(MsgListTable.LAST_DATE)));
				msgListItem.setLastID(cursor.getInt(cursor.getColumnIndex(MsgListTable.LAST_ID)));
				msgListItem.setCount(getCount(userID));
				msgListItem.setHasNew(cursor.getInt(cursor.getColumnIndex(MsgListTable.HAS_NEW)));
				msgListItems.add(msgListItem);
			}
			if (cursorNull)
			{
				//Log.i(TAG, "###cursor is null###");
				msgListItems = null;
			}
			mDatabase.close();
		}
		catch (Exception e)
		{
			// TODO Auto-generated catch block
			//Log.i(TAG, "Exception: " + e.getCause());
			e.printStackTrace();
			msgListItems = null;
		}

		return msgListItems;
	}

	public int getCount(int userID)
	{
		//Log.i(TAG, "-----getCount-----");
		MsgDbManager manager = new MsgDbManager();
		return manager.getMsgNum(userID);
	}

	public void addMsgListItem(MsgListItem msgListItem)
	{
		//Log.i(TAG, "-----addMsgListItem-----");
		try
		{
			String sql = "INSERT INTO " + MsgListTable.TABLE_NAME
					+ "("
					+ MsgListTable.USER_ID + ", "
					+ MsgListTable.NICKNAME + ", "
					+ MsgListTable.LAST_MSG + ", "
					+ MsgListTable.LAST_DATE + ", "
					+ MsgListTable.LAST_ID + ", "
					+ MsgListTable.HAS_NEW
					+ ")"
					+ " VALUES "
					+ "(?,?,?,?,?,?);";
			Object[] bindArgs = new Object[] {
					msgListItem.getUserID(),
					msgListItem.getNickName(),
					msgListItem.getLastMsg(),
					msgListItem.getLastDate(),
					msgListItem.getLastID(),
					msgListItem.getHasNew()};
			mDatabase = mDbHelper.getWritableDatabase();
			mDatabase.execSQL(sql, bindArgs);
			mDatabase.close();
		}
		catch (SQLException e)
		{
			// TODO Auto-generated catch block
			//Log.i(TAG, "Exception: " + e.getCause());
			e.printStackTrace();
		}
	}

	public void addMsgListItems(List<MsgListItem> msgListItems)
	{
		//Log.i(TAG, "-----addMsgListItems-----");
		String sql = "INSERT INTO " + MsgListTable.TABLE_NAME
				+ "("
				+ MsgListTable.USER_ID + ", "
				+ MsgListTable.NICKNAME + ", "
				+ MsgListTable.LAST_MSG + ", "
				+ MsgListTable.LAST_DATE + ", "
				+ MsgListTable.LAST_ID + ","
				+ MsgListTable.HAS_NEW
				+ ")"
				+ " VALUES "
				+ "(?,?,?,?,?);";
		try
		{
			mDatabase = mDbHelper.getWritableDatabase();

			for (MsgListItem msgListItem : msgListItems)
			{
				Object[] bindArgs = new Object[] {
						msgListItem.getUserID(),
						msgListItem.getNickName(),
						msgListItem.getLastMsg(),
						msgListItem.getLastDate(),
						msgListItem.getLastID()};
				mDatabase.execSQL(sql, bindArgs);
			}
			mDatabase.close();
		}
		catch (SQLException e)
		{
			// TODO Auto-generated catch block
			//Log.i(TAG, "Exception: " + e.getCause());
			e.printStackTrace();
		}
	}

	public void updateMsgListItem(MsgListItem msgListItem)
	{
		//Log.i(TAG, "-----updateMsgListItem-----");
		try
		{
			String updateSql = "UPDATE " + MsgListTable.TABLE_NAME
					+ " SET "
					+ MsgListTable.LAST_MSG + "=?, "
					+ MsgListTable.LAST_DATE + "=?, "
					+ MsgListTable.HAS_NEW + "=?"
					+ " WHERE "
					+ MsgListTable.USER_ID + "=?;";
			Object[] bindArgs = new Object[]{
					msgListItem.getLastMsg(),
					msgListItem.getLastDate(),
					msgListItem.getHasNew(),
					msgListItem.getUserID()};

			mDatabase = mDbHelper.getWritableDatabase();
			//Log.i(TAG, updateSql);
			mDatabase.execSQL(updateSql, bindArgs);
			mDatabase.close();
		}
		catch (SQLException e)
		{
			// TODO Auto-generated catch block
			//Log.i(TAG, "Exception: " + e.getCause());
			e.printStackTrace();
		}
	}

	public void deleteMsgListItem(MsgListItem item)
	{
		//Log.i(TAG, "-----deleteMsgListItem-----");
		String deleteSql = "DELETE FROM " + MsgListTable.TABLE_NAME
				+ " WHERE "
				+ MsgListTable.USER_ID
				+ "="
				+ item.getUserID();
		try
		{
			mDatabase = mDbHelper.getWritableDatabase();
			//Log.i(TAG, deleteSql);
			mDatabase.execSQL(deleteSql);
			mDatabase.close();
		}
		catch (Exception e)
		{
			// TODO: handle exception
			//Log.i(TAG, "Exception: " + e.getCause());
			e.printStackTrace();
		}
	}

	public void deleteMsgListItems(List<MsgListItem> items)
	{
		//Log.i(TAG, "-----deleteMsgListItem-----");

		try
		{
			mDatabase = mDbHelper.getWritableDatabase();
			String deleteSql = "";
			for (MsgListItem msgListItem : items)
			{
				deleteSql = "DELETE FROM " + MsgListTable.TABLE_NAME
						+ " WHERE "
						+ MsgListTable.USER_ID
						+ "="
						+ msgListItem.getUserID();
				//Log.i(TAG, deleteSql);
				mDatabase.execSQL(deleteSql);
			}

			mDatabase.close();
		}
		catch (Exception e)
		{
			// TODO: handle exception
			//Log.i(TAG, "Exception: " + e.getCause());
			e.printStackTrace();
		}
	}

	public void deleteMsgListAll()
	{
		//Log.i(TAG, "-----deleteMagListAll-----");
		try
		{
			mDatabase = mDbHelper.getWritableDatabase();
			mDatabase.execSQL("DELETE FROM " + MsgListTable.TABLE_NAME + ";");
			mDatabase.close();
		}
		catch (Exception e)
		{
			// TODO: handle exception
			//Log.i(TAG, "Exception: " + e.getCause());
			e.printStackTrace();
		}
	}

	public boolean hasUser(int userID)
	{
		//Log.i(TAG, "-----hasUser-----");
		String sql = "SELECT * FROM " + MsgListTable.TABLE_NAME
				+ " WHERE "
				+ MsgListTable.USER_ID + "=?;";
		String[] selectionArgs = new String[]{String.valueOf(userID)};
		boolean has = false;
		try
		{
			mDatabase = mDbHelper.getReadableDatabase();
			Cursor cursor = mDatabase.rawQuery(sql, selectionArgs);
			has = cursor.moveToFirst();
			mDatabase.close();
		}
		catch (Exception e)
		{
			// TODO Auto-generated catch block
			//Log.i(TAG, "Exception: " + e.getCause());
			e.printStackTrace();
			has = false;
		}

		return has;
	}

}
